﻿Imports System.Data.OleDb
Imports System.Text

Public Class ToolsDal

    Public Shared Function ZetVoorraadOpNul() As Integer
        Using conn As New OleDbConnection(My.Settings.fairshopConnectionString)
            conn.Open()
            Dim strSQL As String = "UPDATE Artikels SET Artikels.Voorraad = 0"
            Dim cm1 As New OleDbCommand(strSQL, conn)
            Return cm1.ExecuteNonQuery()
        End Using
    End Function

    Public Shared Function KopieerVoorraadNaarVerwachteVoorraad() As Integer
        Using conn As New OleDbConnection(My.Settings.fairshopConnectionString)
            conn.Open()
            Dim strSQL As String = "UPDATE Artikels SET Artikels.VerwachteVoorraad = [Artikels].[Voorraad]"
            Dim cm1 As New OleDbCommand(strSQL, conn)
            Return cm1.ExecuteNonQuery()
        End Using
    End Function

    Public Shared Function ImportArtikels(ByVal strDatabankSource As String, ByVal strDatabankTarget As String, _
            ByVal updateVoorraad As Boolean, ByVal updateVerwachteVoorraad As Boolean) As String
        Dim sb As New StringBuilder()
        Dim dsSource As New ArtikelsDataSet()
        Dim strConnSource As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankSource
        Using connSource As New OleDbConnection(strConnSource)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connSource
            catsAdapter.Fill(dsSource.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connSource
            leveranciersAdapter.Fill(dsSource.Leveranciers)
            Dim artikelsAdapter As New OleDbDataAdapter("SELECT * FROM Artikels WHERE Voorraad > 0", connSource)
            artikelsAdapter.Fill(dsSource, dsSource.Artikels.TableName)
        End Using

        Dim dsTarget As New ArtikelsDataSet()
        Dim strConnTarget As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankTarget
        Using connTarget As New OleDbConnection(strConnTarget)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connTarget
            catsAdapter.Fill(dsTarget.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connTarget
            leveranciersAdapter.Fill(dsTarget.Leveranciers)
            Dim artikelsAdapter As New ArtikelsDataSetTableAdapters.ArtikelsTableAdapter()
            artikelsAdapter.Connection = connTarget
            artikelsAdapter.Fill(dsTarget.Artikels)
            For Each artikelSource As ArtikelsDataSet.ArtikelsRow In dsSource.Artikels
                Dim artikelTarget As ArtikelsDataSet.ArtikelsRow = dsTarget.Artikels.FindById(artikelSource.Id)
                If (artikelTarget Is Nothing) Then
                    sb.AppendLine(String.Format("Toevoegen artikel {0}", artikelSource.Omschrijving))
                    dsTarget.Artikels.ImportRow(artikelSource)
                Else
                    sb.AppendLine(String.Format("Bijwerken voorraad artikel {0}", artikelTarget.Omschrijving))
                    If (updateVoorraad) Then
                        artikelTarget.Voorraad += artikelSource.Voorraad
                    End If
                    If (updateVerwachteVoorraad) Then
                        artikelTarget.VerwachteVoorraad += artikelSource.VerwachteVoorraad
                    End If
                End If
            Next
            Dim aantal As Integer = artikelsAdapter.Update(dsTarget.Artikels)
            sb.AppendLine(String.Format("{0} artikels werden bijgewerkt", aantal))
        End Using
        Return sb.ToString()
    End Function

    Public Shared Function ImportLevering(ByVal strDatabankSource As String, ByVal strDatabankTarget As String, _
            ByVal idLevering As Integer, ByVal updateVoorraad As Boolean, ByVal updateVerwachteVoorraad As Boolean) As String
        Dim sb As New StringBuilder()
        Dim dsSource As New ArtikelsDataSet()
        Dim strConnSource As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankSource
        Using connSource As New OleDbConnection(strConnSource)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connSource
            catsAdapter.Fill(dsSource.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connSource
            leveranciersAdapter.Fill(dsSource.Leveranciers)
            Dim artikelsAdapter As New ArtikelsDataSetTableAdapters.ArtikelsTableAdapter()
            artikelsAdapter.Connection = connSource
            artikelsAdapter.Fill(dsSource.Artikels)
            Dim leveringenAdapter As New ArtikelsDataSetTableAdapters.LeveringenTableAdapter()
            leveringenAdapter.Connection = connSource
            leveringenAdapter.Fill(dsSource.Leveringen)
            Dim leveringDetailsAdapter As New OleDbDataAdapter("SELECT * FROM LeveringDetails WHERE IdLevering = " & idLevering.ToString(), connSource)
            leveringDetailsAdapter.Fill(dsSource, dsSource.LeveringDetails.TableName)
        End Using

        Dim dsTarget As New ArtikelsDataSet()
        Dim strConnTarget As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankTarget
        Using connTarget As New OleDbConnection(strConnTarget)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connTarget
            catsAdapter.Fill(dsTarget.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connTarget
            leveranciersAdapter.Fill(dsTarget.Leveranciers)
            Dim artikelsAdapter As New ArtikelsDataSetTableAdapters.ArtikelsTableAdapter()
            artikelsAdapter.Connection = connTarget
            artikelsAdapter.Fill(dsTarget.Artikels)
            For Each leveringDetailSource As ArtikelsDataSet.LeveringDetailsRow In dsSource.LeveringDetails
                Dim artikelTarget As ArtikelsDataSet.ArtikelsRow = dsTarget.Artikels.FindById(leveringDetailSource.ArtikelsRow.Id)
                sb.AppendLine(String.Format("Bijwerken voorraad artikel {0}", artikelTarget.Omschrijving))
                If (updateVoorraad) Then
                    artikelTarget.Voorraad += leveringDetailSource.Aantal
                End If
                If (updateVerwachteVoorraad) Then
                    artikelTarget.VerwachteVoorraad += leveringDetailSource.Aantal
                End If
            Next
            Dim aantal As Integer = artikelsAdapter.Update(dsTarget.Artikels)
            sb.AppendLine(String.Format("{0} artikels werden bijgewerkt", aantal))
        End Using
        Return sb.ToString()
    End Function

    Public Shared Function ImportVerkoop(ByVal strDatabankSource As String, ByVal strDatabankTarget As String, _
            ByVal idVerkoop As Integer, ByVal updateVoorraad As Boolean, ByVal updateVerwachteVoorraad As Boolean) As String
        Dim sb As New StringBuilder()
        Dim dsSource As New ArtikelsDataSet()
        Dim strConnSource As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankSource
        Using connSource As New OleDbConnection(strConnSource)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connSource
            catsAdapter.Fill(dsSource.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connSource
            leveranciersAdapter.Fill(dsSource.Leveranciers)
            Dim artikelsAdapter As New ArtikelsDataSetTableAdapters.ArtikelsTableAdapter()
            artikelsAdapter.Connection = connSource
            artikelsAdapter.Fill(dsSource.Artikels)
            Dim verkopenAdapter As New ArtikelsDataSetTableAdapters.VerkopenTableAdapter()
            verkopenAdapter.Connection = connSource
            verkopenAdapter.Fill(dsSource.Verkopen)
            Dim verkoopsDetailsAdapter As New OleDbDataAdapter("SELECT * FROM VerkoopsDetails WHERE IdVerkoop = " & idVerkoop.ToString(), connSource)
            verkoopsDetailsAdapter.Fill(dsSource, dsSource.VerkoopsDetails.TableName)
        End Using

        Dim dsTarget As New ArtikelsDataSet()
        Dim strConnTarget As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabankTarget
        Using connTarget As New OleDbConnection(strConnTarget)
            Dim catsAdapter As New ArtikelsDataSetTableAdapters.CategorieenTableAdapter()
            catsAdapter.Connection = connTarget
            catsAdapter.Fill(dsTarget.Categorieen)
            Dim leveranciersAdapter As New ArtikelsDataSetTableAdapters.LeveranciersTableAdapter()
            leveranciersAdapter.Connection = connTarget
            leveranciersAdapter.Fill(dsTarget.Leveranciers)
            Dim artikelsAdapter As New ArtikelsDataSetTableAdapters.ArtikelsTableAdapter()
            artikelsAdapter.Connection = connTarget
            artikelsAdapter.Fill(dsTarget.Artikels)
            For Each verkoopsDetailSource As ArtikelsDataSet.VerkoopsDetailsRow In dsSource.VerkoopsDetails
                Dim artikelTarget As ArtikelsDataSet.ArtikelsRow = dsTarget.Artikels.FindById(verkoopsDetailSource.ArtikelsRow.Id)
                sb.AppendLine(String.Format("Bijwerken voorraad artikel {0}", artikelTarget.Omschrijving))
                If (updateVoorraad) Then
                    artikelTarget.Voorraad -= verkoopsDetailSource.Aantal
                End If
                If (updateVerwachteVoorraad) Then
                    artikelTarget.VerwachteVoorraad -= verkoopsDetailSource.Aantal
                End If
            Next
            Dim aantal As Integer = artikelsAdapter.Update(dsTarget.Artikels)
            sb.AppendLine(String.Format("{0} artikels werden bijgewerkt", aantal))
        End Using
        Return sb.ToString()
    End Function

    Public Shared Function ExecuteScripts(ByVal lines() As String) As String
        Dim sb As New StringBuilder()
        Dim cn As New OleDbConnection(My.Settings.fairshopConnectionString)
        Try
            cn.Open()
            For Each strSQL As String In lines
                sb.Append(String.Format("Begin met uitvoeren commando ""{0}""", strSQL))
                sb.Append(Environment.NewLine)
                ' TODO: deze uitvoering heeft nog een probleem met accenten in de sql
                Dim cm As New OleDbCommand(strSQL, cn)
                Dim aantal As Integer = cm.ExecuteNonQuery()
                sb.Append(String.Format("{0} records werden bijgewerkt", aantal))
                sb.Append(Environment.NewLine)
            Next
        Catch ex As Exception
            sb.Append(ex.Message)
            sb.Append(Environment.NewLine)
        Finally
            cn.Close()
        End Try
        Dim rapport As String = sb.ToString()
        log.WriteInfo(rapport)
        Return rapport
    End Function

End Class
